﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ASBCC.Database.Entity;
/*
namespace Vela3.Database.Service.Custom
{
    public class K2WorkflowService
    {
        
        public static K2.View_ActInstDataTable GetActivityInstances(string ntUser)
        {
            K2.View_ActInstDataTable viewActInstDataTable = new K2.View_ActInstDataTable();
            string sql = "select * from View_ActInst where AIUser=@AIUser";
            SqlParameter[] parameters = {
                    new SqlParameter("@AIUser", SqlDbType.NVarChar)};
            parameters[0].Value = ntUser;
            SqlDataHelper.QueryData(sql, DatabaseManager.K2ConnectionString, viewActInstDataTable, parameters);
            return viewActInstDataTable;
        }

        public static K2.View_ActInstDataTable GetActivityInstances(int procInstID)
        {
            K2.View_ActInstDataTable viewActInstDataTable = new K2.View_ActInstDataTable();
            string sql = "select * from View_ActInst where ProcInstID=@ProcInstID";
            SqlParameter[] parameters = {
                    new SqlParameter("@ProcInstID", SqlDbType.Int)};
            parameters[0].Value = procInstID;
            SqlDataHelper.QueryData(sql, DatabaseManager.K2ConnectionString, viewActInstDataTable, parameters);
            return viewActInstDataTable;
        }

        public static K2.View_ActInstRow GetActivityInstance(int procInstID)
        {
            string sql = "select * from View_ActInst where ProcInstID==@ProcInstID";
            SqlParameter[] parameters = {
                    new SqlParameter("@ProcInstID", SqlDbType.Int)};
            parameters[0].Value = procInstID;
            K2.View_ActInstDataTable viewActInstDataTable = new K2.View_ActInstDataTable();
            SqlDataHelper.QueryData(sql, DatabaseManager.K2ConnectionString, viewActInstDataTable, parameters);
            return viewActInstDataTable != null && viewActInstDataTable.Count > 0 ? (K2.View_ActInstRow)viewActInstDataTable.Rows[0] : null;
        }

        public static K2.View_ActInstRow GetActivityInstance(int procInstID, string ntUser)
        {
            string sql = "select * from View_ActInst where ProcInstID=@ProcInstID and user=@user";
            SqlParameter[] parameters = {
                    new SqlParameter("@ProcInstID", SqlDbType.Int),
                    new SqlParameter("@user", SqlDbType.NVarChar)};
            parameters[0].Value = procInstID;
            parameters[1].Value = ntUser;
            K2.View_ActInstDataTable viewActInstDataTable = new K2.View_ActInstDataTable();
            SqlDataHelper.QueryData(sql, DatabaseManager.K2ConnectionString, viewActInstDataTable, parameters);
            return viewActInstDataTable != null && viewActInstDataTable.Count > 0 ? (K2.View_ActInstRow)viewActInstDataTable.Rows[0] : null;
        }

        public static K2.View_ActInstRow GetActivityInstance(int procInstID, int actInstID)
        {
            string sql = "select * from View_ActInst where ProcInstID=@ProcInstID and ActInstID=@ActInstID";
            SqlParameter[] parameters = {
                    new SqlParameter("@ProcInstID", SqlDbType.Int),
                    new SqlParameter("@ActInstID", SqlDbType.Int)};
            parameters[0].Value = procInstID;
            parameters[1].Value = actInstID;
            K2.View_ActInstDataTable viewActInstDataTable = new K2.View_ActInstDataTable();
            SqlDataHelper.QueryData(sql, DatabaseManager.K2ConnectionString, viewActInstDataTable, parameters);
            return viewActInstDataTable != null && viewActInstDataTable.Count > 0 ? (K2.View_ActInstRow)viewActInstDataTable.Rows[0] : null;
        }

        public static K2.View_ActInstRow GetActivityInstance(string serialNumber)
        {
            string sql = "select * from View_ActInst where Data=@serialNumber";
            SqlParameter[] parameters = {
                    new SqlParameter("@serialNumber", SqlDbType.NVarChar)};
            parameters[0].Value = serialNumber;
            K2.View_ActInstDataTable viewActInstDataTable = new K2.View_ActInstDataTable();
            SqlDataHelper.QueryData(sql, DatabaseManager.K2ConnectionString, viewActInstDataTable, parameters);
            return viewActInstDataTable != null && viewActInstDataTable.Count > 0 ? (K2.View_ActInstRow)viewActInstDataTable.Rows[0] : null;
        }

        public static DataTable GetTimesheetTable(DateTime startTime, DateTime endTime)
        {
            string sql = "select * from Timesheet where [CAL_DATE]>=@StartTime and [CAL_DATE]<=@EndTime order by [CAL_DATE]";
            SqlParameter[] parameters = {
                    new SqlParameter("@StartTime", SqlDbType.DateTime),
                    new SqlParameter("@EndTime", SqlDbType.DateTime)};
            parameters[0].Value = startTime;
            parameters[1].Value = endTime;
            DataSet ds = SqlDataHelper.QueryData(sql, parameters);
            return ds == null || ds.Tables.Count == 0 ? null : ds.Tables[0];
        }

        public static void ChangeProcessStamp(string fromAccount, string toAccount, int procInstID, string actName)
        {
            string sql = @"UPDATE [ProcessStamp] SET [OwnerName]=@ToAccount WHERE [ProcInstID]=@ProcInstID AND [ActName]=@ActName AND [OwnerName]=@FromAccount
                            UPDATE [ProcessStamp] SET [Delegation]=@ToAccount WHERE [ProcInstID]=@ProcInstID AND [ActName]=@ActName AND [Delegation]=@FromAccount";
            SqlParameter[] parameters = {
                    new SqlParameter("@ToAccount", SqlDbType.NVarChar),
                    new SqlParameter("@ProcInstID", SqlDbType.Int),
                    new SqlParameter("@ActName", SqlDbType.NVarChar),
                    new SqlParameter("@FromAccount", SqlDbType.NVarChar)};
            parameters[0].Value = toAccount;
            parameters[1].Value = procInstID;
            parameters[2].Value = actName;
            parameters[3].Value = fromAccount;
            int rows = SqlDataHelper.ExecuteSql(sql, parameters);
        }

        public static DataTable GetObosultedMyActivity(int procInstID, string condition)
        {
            string sql = "select ApprovalHistID from [MyActivity]  with (nolock)  where [ProcessInstanceID] = @ProcInstID " + condition;
            SqlParameter[] parameters = {
                    new SqlParameter("@ProcInstID", SqlDbType.Int)};
            parameters[0].Value = procInstID;
            DataSet ds = SqlDataHelper.QueryData(sql, parameters);
            return ds == null || ds.Tables.Count == 0 ? null : ds.Tables[0];
        }

        public static void UpdateMyActivities(int toStatus, int procInstID, string condition)
        {
            string sql = @"UPDATE [MyActivity] SET [Status]=@Status WHERE [ProcessInstanceID] = @ProcInstID {0}
                        INSERT INTO [MyActivityHist] SELECT *  FROM [MyActivity] with (nolock) WHERE [ProcessInstanceID] = @ProcInstID {0}
                        ";
            sql = string.Format(sql, condition);
            SqlParameter[] parameters = {
                    new SqlParameter("@toStatus", SqlDbType.Int),
                    new SqlParameter("@ProcInstID", SqlDbType.Int)};
            parameters[0].Value = toStatus;
            parameters[1].Value = procInstID;
            SqlDataHelper.ExecuteSql(sql, parameters);
        }

        public static void DeleteMyActivities(int procInstID, string condition)
        {
            string sql = @"DELETE FROM [MyActivity] WHERE [ProcessInstanceID] = @ProcInstID {0}";
            sql = string.Format(sql, condition);
            SqlParameter[] parameters = {
                    new SqlParameter("@ProcInstID", SqlDbType.Int)};
            parameters[0].Value = procInstID;
            SqlDataHelper.ExecuteSql(sql, parameters);
        }

        public static string QueryDelegation(int procInstID, string actName, string ownerName)
        {
            string sql = "SELECT CilAccount FROM TempDelegation WHERE ProcInstID=@ProcInstID AND ActName=@ActName AND AgentPerson=@AgentPerson";
            SqlParameter[] parameters = {
                    new SqlParameter("@ProcInstID", SqlDbType.Int),
                    new SqlParameter("@ActName", SqlDbType.NVarChar,100),
                    new SqlParameter("@AgentPerson", SqlDbType.NVarChar,100)};
            parameters[0].Value = procInstID;
            parameters[1].Value = actName;
            parameters[2].Value = ownerName;
            DataSet ds = SqlDataHelper.QueryData(sql, parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds.Tables[0].Rows[0]["CilAccount"].ToString() : "";
        }

        public static DateTime? QueryDutyDate(DateTime curDate, int dutyDays)
        {
            string sql = "SELECT max(CAL_Date) DutyDate FROM (SELECT TOP {0} CAL_Date FROM Timesheet WHERE CAL_Date>@CAL_Date AND Need_Work=1 ORDER BY CAL_Date) t";
            sql = string.Format(sql, dutyDays.ToString());
            SqlParameter[] parameters = {
                    new SqlParameter("@CAL_Date", SqlDbType.DateTime)};
            parameters[0].Value = curDate;
            DataSet ds = SqlDataHelper.QueryData(sql, parameters);
            if (ds.Tables[0].Rows.Count > 0)
                return DateTime.Parse(ds.Tables[0].Rows[0]["DutyDate"].ToString());

            return null;
        }

        public static void ClearProcessStamp(int procInstID)
        {
            string sql = "UPDATE ProcessStamp SET Invalid=1 WHERE ProcInstID=@ProcInstID";
            SqlParameter[] parameters = {
                    new SqlParameter("@ProcInstID", SqlDbType.Int)};
            parameters[0].Value = procInstID;
            SqlDataHelper.ExecuteSql(sql, parameters);
        }

        public static void RemoveLastProcessStamp(int procInstID)
        {
            string sql = "DELETE ProcessStamp WHERE ProcInstID=@ProcInstID and StampID = (select top 1 StampID from ProcessStamp with (nolock) where ProcInstID=@ProcInstID order by createdate)";
            SqlParameter[] parameters = {
                    new SqlParameter("@ProcInstID", SqlDbType.Int)};
            parameters[0].Value = procInstID;
            SqlDataHelper.ExecuteSql(sql, parameters);
        }

        /// <summary>
        /// 获取代理
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="procInstID"></param>
        /// <param name="actName"></param>
        /// <param name="accountList"></param>
        /// <returns></returns>
        public static DataTable UpdateDelegation(string procName, int procInstID, string actName, string[] accountList)
        {
            string sql = @"
                DELETE FROM TempDelegation WHERE ProcInstID=@ProcInstID AND ActName=@ActivityName
                INSERT INTO TempDelegation
                    SELECT @ProcInstID ProcInstID, @ActivityName ActName, CilAccount,AgentPerson 
                    FROM DelegationView WHERE (0=1{0})
	                    AND SystemNameEn=(SELECT TOP 1 DelegationSettings FROM ProcessConfig WHERE ProcessName=@ProcessName AND ActivityName=@ActivityName)
                SELECT CilAccount,AgentPerson FROM TempDelegation WHERE ProcInstID=@ProcInstID AND ActName=@ActivityName";
            SqlParameter[] parameters = {
                    new SqlParameter("@ProcInstID", SqlDbType.Int),
                    new SqlParameter("@ProcessName", SqlDbType.NVarChar),
                    new SqlParameter("@ActivityName", SqlDbType.NVarChar)};
            parameters[0].Value = procInstID;
            parameters[1].Value = procName;
            parameters[2].Value = actName;

            string sqlClause = string.Empty;
            for (int i = 0; i < accountList.Length; i++)
            {
                sqlClause += " OR CilAccount=" + accountList[i];
            }
            sql = string.Format(sql, sqlClause);
            return SqlDataHelper.QueryData(sql, parameters).Tables[0];
        }

        public static int GetVersionNumberByProcessInstanceID(int procInstId)
        {
            if (procInstId < 0) return -1;
            string sqlSyntax = "select _Proc.Ver from _Proc join _ProcInst on _Proc.ID = _ProcInst.ProcID where _ProcInst.Id=@ProcInstID";
            SqlParameter[] parameters = {
                    new SqlParameter("@ProcInstID", SqlDbType.Int)};
            parameters[0].Value = procInstId;
            DataTable dt = new DataTable();
            SqlDataHelper.QueryData(sqlSyntax, DatabaseManager.K2ConnectionString, dt, parameters);

            string ver = string.Empty;

            if (dt.Rows.Count > 0)
            {
                ver = dt.Rows[0][0].ToString();
            }
            if (string.IsNullOrEmpty(ver))
                return -1;
            return Convert.ToInt32(ver);
        }

        public static int GetDefaultVersionNumberByFullProcName(string fullProcName)
        {
            if (string.IsNullOrEmpty(fullProcName))
                return -1;
            string sqlSyntax = "select _Proc.Ver from _Proc join _ProcSet on _ProcSet.ProcVerID=_Proc.ID where _ProcSet.FullName=@FullName";
            SqlParameter[] parameters = {
                    new SqlParameter("@FullName", SqlDbType.NVarChar)};
            parameters[0].Value = fullProcName;
            DataTable dt = new DataTable();
            SqlDataHelper.QueryData(sqlSyntax, DatabaseManager.K2ConnectionString, dt, parameters);

            string ver = string.Empty;

            if (dt.Rows.Count > 0)
            {
                ver = dt.Rows[0][0].ToString();
            }
            if (string.IsNullOrEmpty(ver))
                return -1;
            return Convert.ToInt32(ver);
        }

        public static DataTable GetWorklistItemRedirect(string serialNumber)
        {
            string sql = @"select wh.ProcInstID, wh.ActInstDestID, wsl.ID AS WorkID, as_.ActionerName as AIUser
from _WorklistHeader as wh 
join _ActionActInstRights as air (nolock) on wh.ProcInstID = air.ProcInstID and wh.ActInstDestID = air.ActInstDestID and air.ActionName='Update'
join _Actioners as as_ (nolock) on air.ActionerID = as_.ID
join _WorklistSlot as wsl on wh.ProcInstID = wsl.ProcInstID and wh.ID = wsl.HeaderID and wh.ActInstID = wsl.ActInstID 
where wh.Data=@SerialNumber";

            SqlParameter[] parameters = {
                    new SqlParameter("@SerialNumber", SqlDbType.NVarChar)};
            parameters[0].Value = serialNumber;
            DataTable dt = new DataTable();
            SqlDataHelper.QueryData(sql, DatabaseManager.K2ConnectionString, dt, parameters);

            if (dt == null || dt.Rows.Count == 0)
                return null;
            return dt;
        }

        public static string GetSerialNumber(int procInstID)
        {
            string sql = "select Data from _WorklistHeader with (nolock) where ProcInstID=@ProcInstID";
            SqlParameter[] parameters = {
                    new SqlParameter("@ProcInstID", SqlDbType.Int)};
            parameters[0].Value = procInstID;
            DataTable dt = new DataTable();
            SqlDataHelper.QueryData(sql, DatabaseManager.K2ConnectionString, dt, parameters);

            return dt.Rows.Count > 0 ? dt.Rows[0]["Data"].ToString() : "";
        }
    }
    
}*/
